package com.example.backend.repository;

import com.example.backend.domain.entity.TicketSLA;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.Instant;
import java.util.List;
import java.util.Optional;

/**
 * 工单SLA跟踪Repository
 */
@Repository
public interface TicketSLARepository extends JpaRepository<TicketSLA, Long> {

    /**
     * 根据工单ID查找SLA记录
     */
    Optional<TicketSLA> findByTicketId(Long ticketId);

    /**
     * 查找违反响应SLA的工单
     */
    @Query("SELECT t.ticketId FROM TicketSLA t WHERE t.firstResponseAt IS NULL AND t.responseDeadline < :now")
    List<Long> findViolatedResponseSLA(@Param("now") Instant now);

    /**
     * 查找违反解决SLA的工单
     */
    @Query("SELECT t.ticketId FROM TicketSLA t WHERE t.resolvedAt IS NULL AND t.resolutionDeadline < :now")
    List<Long> findViolatedResolutionSLA(@Param("now") Instant now);

    /**
     * 查找违反SLA的工单（响应或解决）
     */
    @Query("SELECT t.ticketId FROM TicketSLA t WHERE " +
           "(t.firstResponseAt IS NULL AND t.responseDeadline < :now) OR " +
           "(t.resolvedAt IS NULL AND t.resolutionDeadline < :now)")
    List<Long> findViolatedSLATickets(@Param("now") Instant now);

    /**
     * 查找即将违反SLA的工单
     */
    @Query("SELECT t.ticketId FROM TicketSLA t WHERE " +
           "(t.firstResponseAt IS NULL AND t.responseDeadline <= :warningTime AND t.responseDeadline > :now) OR " +
           "(t.resolvedAt IS NULL AND t.resolutionDeadline <= :warningTime AND t.resolutionDeadline > :now)")
    List<Long> findWarningSLATickets(@Param("warningTime") Instant warningTime, @Param("now") Instant now);

    /**
     * 统计违反响应SLA的记录数
     */
    @Query("SELECT COUNT(t) FROM TicketSLA t WHERE t.firstResponseAt IS NULL AND t.responseDeadline < :now")
    long countViolatedResponseSLA(@Param("now") Instant now);

    /**
     * 统计违反解决SLA的记录数
     */
    @Query("SELECT COUNT(t) FROM TicketSLA t WHERE t.resolvedAt IS NULL AND t.resolutionDeadline < :now")
    long countViolatedResolutionSLA(@Param("now") Instant now);

    /**
     * 根据创建时间范围查找SLA记录
     */
    List<TicketSLA> findByCreatedAtBetween(Instant startDate, Instant endDate);

    /**
     * 查找创建时间早于指定日期的记录
     */
    List<TicketSLA> findByCreatedAtBefore(Instant cutoffDate);

    /**
     * 查找需要警告的SLA记录
     */
    @Query("SELECT t FROM TicketSLA t WHERE " +
           "(t.firstResponseAt IS NULL AND t.responseDeadline <= :warningTime AND t.responseDeadline > :now) OR " +
           "(t.resolvedAt IS NULL AND t.resolutionDeadline <= :warningTime AND t.resolutionDeadline > :now)")
    List<TicketSLA> findWarningSLARecords(@Param("warningTime") Instant warningTime, @Param("now") Instant now);

    /**
     * 查找已超时的SLA记录
     */
    @Query("SELECT t FROM TicketSLA t WHERE " +
           "(t.firstResponseAt IS NULL AND t.responseDeadline < :now) OR " +
           "(t.resolvedAt IS NULL AND t.resolutionDeadline < :now)")
    List<TicketSLA> findExpiredSLARecords(@Param("now") Instant now);

    /**
     * 根据工单ID列表查找SLA记录
     */
    List<TicketSLA> findByTicketIdIn(List<Long> ticketIds);

    /**
     * 删除指定工单的SLA记录
     */
    void deleteByTicketId(Long ticketId);

    /**
     * 批量删除指定工单的SLA记录
     */
    void deleteByTicketIdIn(List<Long> ticketIds);
}
